package org.study.plus.boot.service;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.google.common.collect.Lists;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.util.HtmlUtils;
import org.study.plus.boot.mapper.NoEntityMapper;
import org.study.plus.boot.vo.*;
import org.study.plus.boot.vo.DynamicSqlRequest.Filter;
import org.study.plus.boot.vo.DynamicSqlRequest.OrderBy;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 作用描述
 *
 * @author doveylovey
 * @version v1.0.0
 * @email 1135782208@qq.com
 * @date 2025年08月15日
 */
@Slf4j
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
@Service
public class NoEntityServiceImpl implements NoEntityService {
    private final NoEntityMapper noEntityMapper;

    @Override
    public Page<Map<String, Object>> dynamicPageQuery(long current, long size, String table, String cols, Wrapper<Object> qw) {
        return noEntityMapper.dynamicPageQuery(new Page<>(current, size), cols, table, qw);
    }

    @Override
    public Page<Map<String, Object>> dynamicPageQuery(DynamicSqlRequest dynamicSqlRequest) {
        String sql = this.buildSelect(dynamicSqlRequest);
        log.info("当前SQL语句：{}", sql);
        Page<Map<String, Object>> resultPage = noEntityMapper.dynamicQuerySql(new Page<>(dynamicSqlRequest.getPageParam().getCurrent(), dynamicSqlRequest.getPageParam().getSize()), sql);
        Page<Map<String, Object>> resultPage1 = noEntityMapper.dynamicQueryAnnotation(new Page<>(dynamicSqlRequest.getPageParam().getCurrent(), dynamicSqlRequest.getPageParam().getSize()), sql);
        return resultPage;
    }

    private String buildSelect(DynamicSqlRequest dynamicSqlRequest) {
        List<String> columnList = dynamicSqlRequest.getColumnList();
        String cols = CollectionUtils.isEmpty(columnList) ? "*" : columnList.stream().collect(Collectors.joining(","));
        // SELECT 列 FROM 表
        StringBuilder sql = new StringBuilder("SELECT ")
                .append(cols)
                .append(" FROM ")
                .append(this.escape(dynamicSqlRequest.getTable()));
        // WHERE
        List<Filter> filterList = dynamicSqlRequest.getFilterList();
        if (CollectionUtils.isNotEmpty(filterList)) {
            sql.append(" WHERE ").append(this.where(filterList));
        }
        // GROUP BY
        List<String> groupByList = dynamicSqlRequest.getGroupByList();
        if (CollectionUtils.isNotEmpty(groupByList)) {
            sql.append(" GROUP BY ").append(groupByList.stream().map(this::escape).collect(Collectors.joining(",")));
        }
        // HAVING
        List<Filter> havingList = dynamicSqlRequest.getHavingList();
        if (CollectionUtils.isNotEmpty(havingList)) {
            sql.append(" HAVING ").append(this.where(havingList));
        }
        // ORDER BY
        List<OrderBy> orderByList = dynamicSqlRequest.getOrderByList();
        if (CollectionUtils.isNotEmpty(orderByList)) {
            sql.append(" ORDER BY ").append(orderByList.stream()
                    .map(o -> this.escape(o.getField()) + " " + o.getDirection())
                    .collect(Collectors.joining(",")));
        }
        return sql.toString();
    }

    /**
     * 把 Filter 列表拼成 where 片段
     */
    private String where(List<Filter> filterList) {
        return filterList.stream().map(filter -> {
                    String field = this.escape(filter.getField());
                    switch (filter.getOp()) {
                        case EQ:
                            return field + " = " + this.val(filter.getValue());
                        case NE:
                            return field + " <> " + this.val(filter.getValue());
                        case GT:
                            return field + " > " + this.val(filter.getValue());
                        case GE:
                            return field + " >= " + this.val(filter.getValue());
                        case LT:
                            return field + " < " + this.val(filter.getValue());
                        case LE:
                            return field + " <= " + this.val(filter.getValue());
                        case LIKE:
                            return field + " LIKE " + this.val("%" + filter.getValue() + "%");
                        case LLIKE:
                            return field + " LIKE " + this.val("%" + filter.getValue());
                        case RLIKE:
                            return field + " LIKE " + this.val(filter.getValue() + "%");
                        case IN:
                            return field + " IN (" + this.inVal(filter.getValue()) + ")";
                        case BETWEEN:
                            return this.betweenVal(field, filter.getValue());
                        case IS_NULL:
                            return field + " IS NULL";
                        case NOT_NULL:
                            return field + " IS NOT NULL";
                        default:
                            throw new IllegalArgumentException("unknown op");
                    }
                })
                .collect(Collectors.joining(" AND "));
    }

    private String val(Object value) {
        if (value == null) {
            return "null";
        }
        if (value instanceof Number) {
            return value.toString();
        }
        return "'" + value.toString().replace("'", "''") + "'";
    }

    private String inVal(Object value) {
        if (value instanceof Collection) {
            return ((Collection<?>) value).stream()
                    .map(this::val)
                    .collect(Collectors.joining(","));
        }
        throw new IllegalArgumentException("IN value must be Collection");
    }

    private String betweenVal(String field, Object value) {
        if (value instanceof List && ((List<?>) value).size() == 2) {
            List<?> arr = (List<?>) value;
            return field + " BETWEEN " + val(arr.get(0)) + " AND " + val(arr.get(1));
        }
        throw new IllegalArgumentException("BETWEEN value must be list with size 2");
    }

    /**
     * 简单转义，防止关键字/空格
     */
    private String escape(String s) {
        return "`" + s.replace("`", "``") + "`";
    }

    @Override
    public DatabaseSqlParseVO databaseSqlParse(DatabaseSqlParseDTO databaseSqlParseDTO) {
        List<LinkedHashMap<String, Object>> data = Lists.newArrayList();
        Long totalCount = null;
        try {
            if (databaseSqlParseDTO.getPage() != null) {
                // 需要分页查询时：先查询数据总数，总数大于0时再查询具体的数据列表
                totalCount = noEntityMapper.doCount(this.convertTotalSql(databaseSqlParseDTO));
                if (totalCount != null && totalCount > 0) {
                    data = noEntityMapper.doQuery(this.convertQuerySql(databaseSqlParseDTO));
                }
            } else {
                // 需需要分页查询时：只查询具体的数据列表
                data = noEntityMapper.doQuery(this.convertQuerySql(databaseSqlParseDTO));
                totalCount = CollectionUtils.isEmpty(data) ? 0L : data.size();
            }
            // 后续数据处理
            if (databaseSqlParseDTO.getDatabaseType() == 2) {
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
                for (LinkedHashMap<String, Object> row : data) {
                    if (row.containsKey("time") && row.get("time") instanceof Timestamp) {
                        Timestamp timestamp = (Timestamp) row.get("time");
                        String formattedTime = formatter.format(timestamp);
                        row.put("time", formattedTime);
                    }
                }
            }
        } catch (Exception ex) {
            log.error("查询数据出错", ex);
            throw new RuntimeException("查询数据出错", ex);
        }
        DatabaseSqlParseVO databaseSqlParseVO = new DatabaseSqlParseVO();
        databaseSqlParseVO.setPage(databaseSqlParseDTO.getPage());
        databaseSqlParseVO.setSize(databaseSqlParseDTO.getSize());
        databaseSqlParseVO.setTotalCount(totalCount == null ? 0L : totalCount);
        databaseSqlParseVO.setTotalPage(0);
        if (totalCount != null && totalCount > 0) {
            // databaseSqlParseVO.setTotalCount(totalCount);
            if (databaseSqlParseDTO.getSize() != null && databaseSqlParseDTO.getSize() > 0) {
                databaseSqlParseVO.setTotalPage((int) Math.ceil((double) totalCount / databaseSqlParseDTO.getSize()));
            }
        }
        databaseSqlParseVO.setData(data);
        return databaseSqlParseVO;
    }

    private String convertTotalSql(DatabaseSqlParseDTO databaseSqlParseDTO) {
        StringBuilder sql = new StringBuilder("SELECT count(*) ");
        sql.append("FROM ")
                .append("(")
                .append(this.convertQuerySqlWithoutPage(databaseSqlParseDTO))
                .append(") TOTAL");

        log.info("当前SQL语句(查询数据条数)：{}", sql);
        return sql.toString();
    }

    private String convertQuerySqlWithoutPage(DatabaseSqlParseDTO databaseSqlParseDTO) {
        String fieldClause = "*";
        if (CollectionUtils.isNotEmpty(databaseSqlParseDTO.getSelectFiledList())) {
            fieldClause = String.join(", ", databaseSqlParseDTO.getSelectFiledList());
        }
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ")
                .append(fieldClause)
                .append(" FROM ")
                .append(databaseSqlParseDTO.getTableName());
        if (CollectionUtils.isNotEmpty(databaseSqlParseDTO.getWhereFiledList())) {
            StringJoiner whereClause = new StringJoiner(" AND ");
            for (String condition : databaseSqlParseDTO.getWhereFiledList()) {
                whereClause.add(HtmlUtils.htmlUnescape(condition));
            }
            sql.append(" WHERE ").append(whereClause);
        }
        if (CollectionUtils.isNotEmpty(databaseSqlParseDTO.getGroupByFieldList())) {
            sql.append(" GROUP BY ").append(String.join(", ", databaseSqlParseDTO.getGroupByFieldList()));
        }
        if (CollectionUtils.isNotEmpty(databaseSqlParseDTO.getOrderByFieldList())) {
            sql.append(" ORDER BY ").append(String.join(", ", databaseSqlParseDTO.getOrderByFieldList()));
        }
        return sql.toString();
    }

    private String convertQuerySql(DatabaseSqlParseDTO databaseSqlParseDTO) {
        StringBuilder sql = new StringBuilder(this.convertQuerySqlWithoutPage(databaseSqlParseDTO));
        if (databaseSqlParseDTO.getSize() != null) {
            if (databaseSqlParseDTO.getDatabaseType() == 1) {
                // MySQL数据库
                sql.append(" LIMIT ").append(databaseSqlParseDTO.getSize());
                if (databaseSqlParseDTO.getPage() != null) {
                    sql.append(" OFFSET ").append(databaseSqlParseDTO.getPage());
                }
            } else if (databaseSqlParseDTO.getDatabaseType() == 2) {
                // TDengine时序库
                sql.append(" LIMIT ");
                if (databaseSqlParseDTO.getPage() != null) {
                    sql.append(databaseSqlParseDTO.getPage()).append(", ").append(databaseSqlParseDTO.getSize());
                } else {
                    sql.append(databaseSqlParseDTO.getSize());
                }
            } else {
                throw new IllegalArgumentException("不支持的数据库类型: " + databaseSqlParseDTO.getDatabaseType());
            }
        }
        log.info("当前SQL语句(查询具体数据)：{}", sql);
        return sql.toString();
    }
}
